Data items stored in a database may be accessed by an application via a database server. For example, an application may send a statement to a database server to read a data item from the database. The statement may also indicate that the application may subsequently update the data item via a write operation. Database servers may utilize pessimistic locking to control access to data items stored in a database. In pessimistic locking, an exclusive lock is obtained with respect to a data item when the database server receives a statement related to reading and/or updating the particular data item. An exclusive lock of a particular data item begins at the initial read of the data item and persists until the time that the update occurs or until the application releases the data item without making an update. During an exclusive lock, other applications are not able to access the data item. For example, a database server will not permit access to another application attempting to read or update a data item associated with an exclusive lock. Restricting access to a data item using pessimistic locking can create a backlog of applications requesting access to the data item.
Database servers may also utilize optimistic locking to control access to data items stored in a database. Optimistic locking allows an application to obtain a shared lock during an initial read and does not involve obtaining an exclusive lock on the data item until the application makes the update to the data item. During a shared lock, other applications may also access a data item for a read operation, but write operations are executed after obtaining an exclusive lock for a particular data item. At the time of an update, a database server utilizing optimistic locking determines if the data item has been changed from the initial read. If the data item has changed, then the update will fail. Otherwise, the database server executes the update statement.
In both pessimistic locking and optimistic locking, the application remains online with respect to the database server, that is, a connection between the application and the database server persists from the time of the initial read until the time of an update or until the application releases the data item without making an update. If the application goes offline with respect to the database server, that is, the connection between the application and the database server terminates, then a pending read or update operation with respect to a particular data item is re-submitted. A persistent connection between the application and the database server for each read and/or update operation limits the number of applications that can open connections with the database server to update a data item stored in a particular database. Additionally, when an update is to be made by an application with respect to a data item, the application separately requests optimistic locking information, such as a time stamp or version number, from a database associated with the data item to execute a subsequent update to the data item.